/*
 * TCSS360 
 * TESK. 
 */
package SQL;

import java.sql.Connection;    
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * This class handles all the database requirements for the project.
 * @author Shruti More
 * @version 3/20/2014.
 */
public class DB {

	/**
	 * ip address = 128.208.244.213
	 *repos.insttech.washington.edu
	 *default port = 3306
	 */
	private static final String url = "jdbc:mysql://128.208.244.213:3306/shrutm";
	private static final String user = "shrutm";
	private static final String pass = "WonIchWo";

	private static Connection con = null;
	private static ArrayList<String> confList = null;

	public DB(){
		/*try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		 */
		/*ArrayList<String> subs = new ArrayList<String>();
		subs.add("Toshi");
		ArrayList<String> reviewers = new ArrayList<String>();
		reviewers.add("Kon");

		register("Thang", "Tran");
		register("Kon", "Nyguen");
		register("Toshi", "Mario");
		register("Emily", "Linniman");


		createAConference("IEEE", "Emily");
		createAConference("WiCS", "Shruti");
		addSubChairToConf("IEEE", subs);
		addReviewersToConf("IEEE", reviewers);

		addPaper("Thang", "IEEE", "Paper1", 1);
		addReview("Thang", "IEEE", "Review1", 1);*/

	}

	/**
	 * This method takes in a first name and a last name
	 * and registers the user into the system.
	 * The role of this user is set to "user" by default.
	 */
	public void register(String firstName, String lastName){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		String sql = "INSERT INTO registration "+
				"VALUES ("+ 0 +", \'"+firstName+"\', \'"+lastName+"\')";
		execute(sql);
		addRole(firstName, "user");
	}

	/**
	 * Checks if the first name and last name exists in the database.
	 */
	public static boolean entryExists(String firstName, String lastName){
		ResultSet r = findByName(firstName, "registration");
		try {
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				con = DriverManager.getConnection(url, user, pass);
			} catch(SQLException se) {
				se.printStackTrace();
			} catch(Exception e) {
				e.printStackTrace();
			}
			if(r.first()){
				if(r.getString("name_first").equals(firstName) &&
						r.getString("name_last").equals(lastName)){
					return true;
				}
			}
			return false;
		} catch (SQLException e) {
			return false;
		}
	}

	/**
	 * This method will fetch the conferences and return as 
	 * an ArrayList of strings.
	 */
	public static ArrayList<String> getConferences(){
		String sql = "SELECT * FROM conferences;";
		return getList(sql, "conf_name");
	}

	/**
	 * This method will fetch the reviewers and return as 
	 * an ArrayList of strings.
	 */
	public static ArrayList<String> getReviewers(String conferenceName){
		ArrayList<String> ans = new ArrayList<String>();
		String sql = "SELECT * FROM " + conferenceName + ";";
		Statement st = null;
		ResultSet r = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			r = st.executeQuery(sql);
			boolean t = r.first();
			String s = "";
			while(t){
				ans.add(r.getString("fName"));
				s = r.getString("role");
				if(!s.equals("reviewer")){
					ans.remove(ans.size() - 1);
				}
				t = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;
	}

	/**
	 * This method will fetch the subChairs and return as 
	 * an ArrayList of strings.
	 */
	public static ArrayList<String> getSubChairs(String conferenceName){
		ArrayList<String> ans = new ArrayList<String>();
		String sql = "SELECT * FROM " + conferenceName + ";";
		Statement st = null;
		ResultSet r = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			r = st.executeQuery(sql);
			boolean t = r.first();
			String s = "";
			while(t){
				ans.add(r.getString("fName"));
				s = r.getString("role");
				if(!s.equals("subChair")){
					ans.remove(ans.size() - 1);
				}
				t = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;
	}

	/**
	 * This method will fetch the progChairs and return as 
	 * an ArrayList of strings.
	 */
	public static ArrayList<String> getProgChairs(String conferenceName){
		String sql = "SELECT * FROM conferences WHERE conf=\"" + conferenceName + "\";";
		return getList(sql, "PCname");
	}

	/**
	 * Call this method only once at the very beginning to add all the 
	 * conferences in the system.
	 */
	public void createAConference(String confName, String programChair){
		Statement st = null;
		if(confList == null){
			confList = new ArrayList<String>();
		}
		confList.add(confName);
		String sql = "INSERT INTO conferences "+
				"VALUES (\"" + confName + "\", \"" + programChair + "\");";

		String conf = "CREATE TABLE " + confName + 
				" (fName VARCHAR(40) NOT NULL, role VARCHAR(20) NOT NULL);";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();

			st.executeUpdate(conf);
			st.executeUpdate(sql);


			st.executeUpdate("INSERT INTO " + confName + 
					" VALUES (\'"+programChair +"\', \'programChair\')");
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		addRole(programChair, "programChair");
		createProgramChairTable(programChair);
	}

	/**
	 * This method will add sub chairs to a conference.
	 */
	public void addSubChairToConf(String confName, ArrayList<String> subChairs){
		Statement st = null;
		createSubChairTables(subChairs);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			for (int j = 0; j < subChairs.size(); j++){
				st.executeUpdate("INSERT INTO " + confName + 
						" VALUES (\'"+ subChairs.get(j) +"\', \'subChair\')");
				addRole(subChairs.get(j), "subChair");
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method adds the reviewers to a given conference.
	 */
	public void addReviewersToConf(String confName, ArrayList<String> reviewers){
		Statement st = null;
		createReviewerTables(reviewers);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			for (int k = 0; k < reviewers.size(); k++){
				st.executeUpdate("INSERT INTO " + confName + 
						" VALUES (\'"+ reviewers.get(k) +"\', \'reviewer\')");
				addRole(reviewers.get(k), "reviewer");
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will delete the paper submitted by the author.
	 */
	public static void deletePaper(String firstNameOfAuthor, String conferenceName,
			String fileName, String progChair, String subChair, String reviewer){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		String sql1 = "UPDATE " + firstNameOfAuthor + " SET paper " +
				" = " + null + " WHERE paper = \"" + fileName + "\"" +
				" AND conf = \"" + conferenceName + "\"";

		String sql2 = "UPDATE papers SET file " +
				" = " + null + " WHERE file = \"" + fileName + "\"" +
				" AND conf = \"" + conferenceName + "\"" +
				" AND authorFName = \"" + firstNameOfAuthor + "\"";

		execute(sql1);
		execute(sql2);

		ArrayList<String> s = getReview(firstNameOfAuthor, conferenceName, fileName);
		String sql3 = "";
		for(int i = 0; i < s.size(); i++){
			sql3 = "DELETE FROM reviews WHERE authorFName = \"" + firstNameOfAuthor +
					"\" AND confName = \"" + conferenceName + "\" AND paperName =\"" + fileName + "\"" +
					" LIMIT 1;";
			execute(sql3);
		}
		String sql4 = "DELETE FROM " + subChair + "SC WHERE conf = \"" + 
				conferenceName + "\" AND authorFName = \"" + firstNameOfAuthor +
				"\" AND paper =\"" + fileName + "\"" +
				" LIMIT 1;";
		execute(sql4);
		
		String sql5 = "DELETE FROM " + progChair + "PC WHERE authorFName = \"" +
				firstNameOfAuthor +	"\" AND paper =\"" + fileName + "\"" +
				" LIMIT 1;";
		execute(sql5);
		
		String sql6 = "DELETE FROM reviewers WHERE conf = \"" + conferenceName +
				"\" AND firstName = \"" + reviewer + "\" AND paper = \"" + fileName +
				"\" AND author = \"" + firstNameOfAuthor + "\""+
				" LIMIT 1;";
		execute(sql6);
		
		String sql7 = "DELETE FROM " + reviewer + "Reviewer WHERE conf = \"" + conferenceName +
				"\" AND authorFName = \"" + firstNameOfAuthor + "\" AND paper = \"" + fileName +
				"\" LIMIT 1;";
		execute(sql7);
	}

	/**
	 * This method will add a paper to a created author table.
	 * An author can add multiple papers to a conference but the
	 * table only stores one paper in that paper category for a 
	 * author and a conference. So the new edited papers submitted, 
	 * will replace the old. 
	 */
	public void addPaper(String firstNameOfAuthor, String conferenceName, 
			ArrayList<String> fileList){
		Statement st = null;
		String sql = "";
		String sql1 = "";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			if(getRole(firstNameOfAuthor).equals("user")){
				addRole(firstNameOfAuthor, "author");
				String temp = "";
				createAuthorTable(firstNameOfAuthor);
				for(int k = 0; k < confList.size(); k++){
					for(int i = 0; i < 4; i++){
						temp = "INSERT INTO papers" + 
								" VALUES (" + i + ", \"" + confList.get(k) + 
								"\", \"" + firstNameOfAuthor + "\", \"" + fileList.get(i) + "\");";
						st.executeUpdate(temp);
					}
				}
			} 

			for(int j = 0; j < 4; j++){
				if(fileList.get(j) != null && !fileList.get(j).equals("")){
					sql = "UPDATE " + firstNameOfAuthor + " SET paper=\""+ 
							fileList.get(j) +"\" WHERE paperNum= " + (j) +
							" AND conf=\"" + conferenceName +"\";" ;
					st.executeUpdate(sql);

					sql1 = "UPDATE papers SET file=\""+ fileList.get(j) +
							"\" WHERE paperNum= " + (j) +
							" AND conf=\"" + conferenceName + 
							"\" AND authorFName=\"" + firstNameOfAuthor +"\";" ;
					st.executeUpdate(sql1);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Helper method to do the execution of the sql statement.
	 */
	private static void execute(String sq){
		Statement st = null;
		try {
			st = con.createStatement();
			st.executeUpdate(sq);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will add a review to an author's submitted paper.
	 */
	public void addReview(String firstNameOfAuthor, String conferenceName, 
			String reviewFileName, String paperName){
		Statement st = null;
		String sql = "INSERT INTO reviews VALUES (\"" + firstNameOfAuthor +"\", \"" 
				+ conferenceName + "\", \"" + paperName + "\", \"" + reviewFileName + "\");";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}		
	}

	/**
	 * This method can be used to get the review associated with the author, 
	 * conference name, and the paper.
	 */
	public static ArrayList<String> getReview(String firstNameOfAuthor, 
			String conferenceName, String paperName){
		String sql = "SELECT * FROM reviews WHERE authorFName=\"" + firstNameOfAuthor + 
				"\" AND confName=\"" + conferenceName +"\" AND paperName=\"" + paperName + "\";" ;
		return getList(sql, "reviewFile");
	}

	/**
	 * Call this when a user becomes a author and submits a paper
	 * for the first time.
	 */
	private static void createAuthorTable(String firstNameOfAuthor){
		Statement st = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			String ins = "";


			String sql = "CREATE TABLE " + firstNameOfAuthor + 
					" (paperNum INT NOT NULL, conf VARCHAR(40) NOT NULL,"+
					" paper text);";
			st.executeUpdate(sql);

			for (int i = 0; i < confList.size(); i++){
				for(int j = 0; j < 4; j++){
					ins = "INSERT INTO " + firstNameOfAuthor + 
							" VALUES (" + j + ",\""+ confList.get(i) +"\", " + null + ");"; 
					st.executeUpdate(ins);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will return all the papers submitted by the author
	 * in the conference.
	 */
	public static ArrayList<String> getAuthorPapersForConf(String authorFName,
			String conf){
		Statement st = null;
		ArrayList<String> ans = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();

			String sql = "SELECT * FROM " + authorFName + ";";
			ResultSet r = st.executeQuery(sql); 
			boolean y = r.first();
			while(y){
				String s = r.getString("conf");
				String p = r.getString("paper");
				if(s.equals(conf)){
					if(p == null){
						p = "";
					}
					ans.add(p);	
				}
				y = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;	
	}

	/**
	 * This method will return all the papers submitted by the author
	 * in any conferences.
	 */
	public static ArrayList<String> getAuthorPapers(String authorFName){
		Statement st = null;
		ArrayList<String> ans = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();

			String sql = "SELECT * FROM " + authorFName + ";";
			ResultSet r = st.executeQuery(sql); 
			boolean y = r.first();
			while(y){
				String p = r.getString("paper");
				ans.add(p);
				y = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;	
	}

	/**
	 * This method will take in a file name and a conference name 
	 * to return the author's first name.
	 */
	public String getAuthor(String filename, String confName){
		String sql = "SELECT * FROM papers WHERE conf = \"" +
				confName + "\" AND file = \"" + filename + "\";";
		return getName(sql, "authorFName");
	}

	/**
	 * This method will take in a file name and a conference name 
	 * to return the author's first name.
	 */
	public String getReviewer(String filename, String confName, 
			String authorFName){
		String sql = "SELECT * FROM reviewers WHERE conf = \"" +
				confName + "\" AND paper = \"" + filename + "\" AND " +
				"author = \"" + authorFName + "\";";
		return getName(sql, "firstName");
	}


	/**
	 * Once the reviewers are assigned for conferences and the 
	 * conference object has been made, call this method only once
	 * to create table with conference name, author name, paper 
	 * assigned columns for each reviewer.
	 */
	private void createReviewerTables(ArrayList<String> reviewers){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		for (int i = 0; i < reviewers.size(); i++){
			String sql = "CREATE TABLE " + reviewers.get(i) + "Reviewer" +
					" (conf VARCHAR(40) NOT NULL,"+
					" authorFName VARCHAR(40)," +
					" paper text);";
			execute(sql);
		}		
	}


	/**
	 * Use this method to assign reviewers to papers.
	 */
	public void assignReviewer(String reviewerFirstName, 
			String conferenceName, String authorFirstName, String paperName){
		Statement st = null;
		String ins = "INSERT INTO " + reviewerFirstName + "Reviewer" +
				" VALUES (\""+ conferenceName +"\", \"" + authorFirstName + 
				"\", \"" + paperName + "\")";
		
		String sql3 = "INSERT INTO reviewers VALUES (\"" +
				conferenceName +  "\", \"" + reviewerFirstName + "\", \"" +
				paperName + "\", \"" + authorFirstName + "\");";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(ins);
			//st.executeUpdate(sql1);
			//st.executeUpdate(sql2);
			st.executeUpdate(sql3);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will return an array list of strings of 
	 * all the papers assigned to a reviewer.
	 */
	public static ArrayList<String> getPapersAssignedForReviewer(String reviewerFName){
		String sql = "SELECT * FROM " + reviewerFName + "Reviewer;";
		return getList(sql, "paper");
	}


	/**
	 * Once the SubChairs are assigned for conferences and the 
	 * conference object has been made, call this method only once
	 * to create table with conference name, author name, paper 
	 * assigned columns for each SubChair.
	 */
	private void createSubChairTables(ArrayList<String> subChair){
		String sql = "";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		for (int i = 0; i < subChair.size(); i++){
			sql = "CREATE TABLE " + subChair.get(i) + 
					"SC (conf VARCHAR(40) NOT NULL,"+
					" authorFName VARCHAR(40) NOT NULL," +
					" paper text," +
					" recommendation text);";
			execute(sql);
		}
	}

	/**
	 * Use this method to assign SubChair to papers.
	 */
	public static void assignSubChairToPaper(String subChairFirstName, 
			String conferenceName, String authorFirstName, String paperName){
		Statement st = null;
		String sql = "INSERT INTO " + subChairFirstName + "SC VALUES (" +
				"\"" + conferenceName + "\", \"" + authorFirstName+"\", " +
				"\"" + paperName +"\", " + -1 + ");" ;
		String sql1 = "INSERT INTO subChairs VALUES (\"" +
				conferenceName +  "\", \"" + subChairFirstName + "\", \"" +
				paperName + "\", \"" + authorFirstName + "\");";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will take in a file name and a conference name 
	 * to return the subChair's first name.
	 */
	public String getSubChairForPaper(String filename, String confName, 
			String authorFName){
		String sql = "SELECT * FROM subChairs WHERE conf = \"" +
				confName + "\" AND paper = \"" + filename + "\" AND " +
				"author = \"" + authorFName + "\";";
		return getName(sql, "firstName");
	}

	/**
	 * The default recommendation is set to -1 which means there
	 * has been no recommendation given yet.
	 * Pass recommendation as 0 for not recommending or 
	 * 1 for recommending.
	 */
	public void addRecommendation(String subChairFirstName, String conferenceName,
			String authorFirstName, String recommendation){
		Statement st = null;
		String sql = "UPDATE " + subChairFirstName + "SC SET " +
				"recommendation=\""+recommendation+"\" WHERE " +
				"authorFName=\'"+authorFirstName+"\' AND " +
				"conf=\"" + conferenceName +"\";" ;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method will return a -1 if no recommendation is given.
	 * It will return a 0 if not recommended or 1 if recommended.
	 */
	public String getRecommendation(String subChairFirstName, String conferenceName,
			String authorFirstName){
		String i = "";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			Statement st = con.createStatement();
			String sql = "SELECT * FROM " + subChairFirstName + "SC " +
					"WHERE authorFName=\'" + authorFirstName + "\' AND " +
					"conf=\"" + conferenceName + "\";" ;
			ResultSet r = st.executeQuery(sql);
			r.first();
			i = r.getString("recommendation");
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return i;
	}

	/**
	 * This method will return an array list of strings of 
	 * all the papers assigned to a sc.
	 */
	public static ArrayList<String> getPapersAssignedForSC(String scFName){
		String sql = "SELECT * FROM " + scFName + "SC;";
		return getList(sql, "paper");
	}

	/**
	 * Once the ProgChair is assigned for conferences and the 
	 * conference object has been made, call this method only once
	 * to create table with conference name, author name, paper 
	 * assigned, and accept status columns for each ProgChair.
	 */
	private void createProgramChairTable(String progChairFirstName){
		Statement st = null;
		String sql = "CREATE TABLE " + progChairFirstName + 
				"PC (authorFName VARCHAR(40) NOT NULL," +
				" paper text," +
				" accept INT);";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Use this method to assign ProgChair to papers.
	 */
	public void assignProgChair(String progChairFirstName,
			String authorFirstName, String paperName){
		Statement st = null;
		String sql = "INSERT INTO " + progChairFirstName + "PC VALUES (\"" + 
				authorFirstName+"\", \"" + paperName +"\", " + null + ");" ;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * Returns the program chair's first name for that conference.
	 */
	public String getProgChair(String conference){
		
		//ArrayList<String> ans = new ArrayList<String>();
		String ans = "";
		String sql = "SELECT * FROM " + conference + ";";
		Statement st = null;
		ResultSet r = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			r = st.executeQuery(sql);
			boolean t = r.first();
			String s = "";
			while(t){
				ans = r.getString("fName");
				s = r.getString("role");
				if(s.equals("programChair")){
					return ans;
				}
				t = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;
	}

	/**
	 * The default acceptance is set to -1 which means there
	 * has been no acceptance status given yet.
	 * Pass acceptance as 0 for not accepting or 
	 * 1 for accepting.
	 */
	public void addAcceptanceStatus(String progChairFirstName, String paper,  
			String authorFirstName, int acceptance){
		Statement st = null;
		String sql = "UPDATE " + progChairFirstName + "PC SET " +
				"accept="+acceptance+" WHERE " +
				"authorFName=\'"+authorFirstName+"\'" +
				" AND paper=\'"+paper+"\';" ;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method returns the conference name when the prog chair
	 * name has been passed as a parameter.
	 */
	public String getConference(String progChair){
		String sql = "SELECT * FROM conferences WHERE " +
				" PCname = \"" + progChair + "\";";
		return getName(sql, "conf_name");
	}

	/**
	 * This method will return a -1 if no acceptance is given.
	 * It will return a 0 if not accepted or 1 if accepted.
	 */
	public int getAcceptanceStatus(String progChairFirstName, String paper,
			String authorFirstName){
		int i = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			Statement st = con.createStatement();
			String sql = "SELECT * FROM " + progChairFirstName + "PC " +
					"WHERE authorFName=\'"+authorFirstName+"\'" +
					" AND paper=\'"+paper+"\';" ;
			ResultSet r = st.executeQuery(sql);
			r.first();
			i = r.getInt("accept");
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return i;
	}

	/**
	 * This method will return an array list of strings of 
	 * all the papers assigned to a pc.
	 */
	public static ArrayList<String> getPapersAssignedForPC(String pcFName){
		String sql = "SELECT * FROM " + pcFName + "PC;";
		return getList(sql, "paper");
	}

	/**
	 * Helper method for getting a string.
	 */
	private static String getName(String sql, String label){
		String ans = "";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			Statement st = con.createStatement();
			ResultSet r = st.executeQuery(sql);
			r.first();
			ans = r.getString(label);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;
	}

	/**
	 * Helper method for getting array lists.
	 */
	private static ArrayList<String> getList(String sql, String label){
		ArrayList<String> ans = new ArrayList<String>();
		Statement st = null;
		ResultSet r = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			r = st.executeQuery(sql);
			boolean t = r.first();
			while(t){
				ans.add(r.getString(label));
				t = r.next();
			}
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ans;
	}

	/**
	 * This method will take any name (first or last) in any case and
	 * search the whole registration table for the entry
	 */
	private static ResultSet findByName(String searchName, String table){
		Statement st = null;
		ResultSet r = null;
		String sql = "";

		if(table == "registration"){
			sql = "SELECT * FROM registration WHERE " +
					"user_ID LIKE \'%" + searchName + "%\' OR " +
					"name_first LIKE \'%" + searchName + "%\' OR " +
					"name_last LIKE \'%" + searchName + "%\';";
		} else if(table == "conferences"){
			sql = "SELECT * FROM conferences WHERE " +
					"conf_name LIKE \'%" + searchName + "%\' OR " +
					"PCname LIKE \'%" + searchName + "%\';";
		} else if(table == "roles"){
			sql = "SELECT * FROM roles WHERE " +
					"fName LIKE \'%" + searchName + "%\' OR " +
					"role LIKE \'%" + searchName + "%\';";
		}
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			r = st.executeQuery(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return r;
	}

	/**
	 * Returns the role of the person associated with the first name.
	 */
	public static String getRole(String firstName){
		String role = "";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			ResultSet s = findByName(firstName, "roles");
			s.first();
			role = s.getString("role");
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return role;
	}

	/**
	 * This method  is used by createConference method.
	 * It takes in the first name and a role 
	 * (either "author", "programChair", "subChair", or "reviewer")
	 * The default role is set to user.
	 */
	private static void addRole(String firstName, String role){
		Statement st = null;
		String sql = "";
		if(role.equals("user")){
			sql = "INSERT INTO roles "+
					"VALUES (\""+ firstName +"\", \"" + role + "\")";
		} else {
			sql = "UPDATE roles SET role=\""+ role +"\" WHERE " +
					"fName=\"" + firstName +"\";" ;
		}
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, user, pass);
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch(SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

}
